package cn.com.bo.util.mysql;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import cn.com.bo.util.PropUtils;
import cn.com.bo.util.redis.JedisUtil;

import com.sun.mail.util.PropUtil;


public class MysqlUtil {
	
	
	private static Logger logger = LoggerFactory.getLogger(MysqlUtil.class);
	
	public static final char UNDERLINE = '_'; 
	
	//三大核心接口
    private static Connection conn = null;
    private static PreparedStatement pstmt = null;
    private static ResultSet rs = null;
	
	
	private static BasicDataSource ds = null;
	
	static {
		init();
	}
	
	private static void init(){
		// 导入配置信息
		try {
			// DBCP核心类
			ds = new BasicDataSource();
			ds.setUsername(PropUtils.get("mysql.username"));
			ds.setPassword(PropUtils.get("mysql.password"));
			ds.setDriverClassName("com.mysql.jdbc.Driver");
			ds.setUrl(PropUtils.get("mysql.url"));
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
	
	public static void setDataSource(BasicDataSource ds1) {
		ds = ds1;
	}
	
	public static void reloadSource() {
		init();
	}
	
	private static DataSource getDataSource() {
		return ds;
	}
	
	private static Connection getConnection() throws SQLException {
		conn = ds.getConnection();
		return conn;
	}
	
	
	//method2：关闭数据库的方法
	private static void closeConn(){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(pstmt!=null){
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
	public static int execOther(String sql){
        try {
        	logger.info("sql = {"+sql+"}");
        	conn = getConnection();  
        	pstmt = conn.prepareStatement(sql);    
            //1、使用Statement对象发送SQL语句
            int affectedRows = pstmt.executeUpdate();
            //2、返回结果
            return affectedRows;
        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        }finally {
        	closeConn();  
        }
    }
    
	/** 
     * 按条件查找 
     * @since  2016年10月20日  
     * @param clazz 
     * @param sql 
     * @return 
     * @throws Exception 
     */  
    public static <T> List<T> find(Class<T> clazz, String sql) {  
        if (sql == null || sql.length() == 0) {  
            logger.warn("查询sql语句不能为空");  
            return new ArrayList<T>();  
        }  
        logger.info("sql = {"+sql+"}");
        try {  
        	conn = getConnection();  
        	pstmt = conn.prepareStatement(sql);    
        	rs = pstmt.executeQuery(sql);  
            return (List<T>) handler(clazz, rs);  
        } catch (Exception e) {  
            logger.error("sql = {"+sql+"}执行出错,Exception = {"+e.getLocalizedMessage()+"}");  
        } finally {  
        	closeConn();  
        }  
        return null;
    }  
    
    public static <T> T findOne(Class<T> clazz, String sql) {  
        if (sql == null || sql.length() == 0) {  
            logger.warn("查询sql语句不能为空");  
            return null;  
        }  
        logger.info("sql = {"+sql+"}");
        try {  
        	conn = getConnection();  
        	pstmt = conn.prepareStatement(sql);    
        	rs = pstmt.executeQuery(sql);  
            List<T> list = (List<T>) handler(clazz, rs);  
            if (list!=null && list.size() > 0) {
            	 return list.get(0);
			}
        } catch (Exception e) {  
            logger.error("sql = {"+sql+"}执行出错,Exception = {"+e.getLocalizedMessage()+"}");  
        } finally {  
        	closeConn();  
        }  
        return null;
    }  
    
    /** 
     * 按条件查找 
     * @since  2016年10月20日  
     * @param clazz 
     * @param sql 
     * @return 
     * @throws Exception 
     */  
    public static <T> List<T> find(T cla, String sql) {  
    	
    	logger.info("sql = {"+sql+"}");
    	// 创建SQL执行工具
		QueryRunner qRunner = new QueryRunner();

		List<T> list = null;
		try {
			list = qRunner
					.query(MysqlUtil.getConnection(),sql,new BeanListHandler<T>((Class<T>) cla.getClass()));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {  
        	closeConn();  
        } 
		return list;
    }  
    
    public static <T> T findOne(T cla, String sql) {  
    	logger.info("sql = {"+sql+"}");
    	// 创建SQL执行工具
		QueryRunner qRunner = new QueryRunner();

		List<T> list = null;
		try {
			list = qRunner
					.query(MysqlUtil.getConnection(),sql,new BeanListHandler<T>((Class<T>) cla.getClass()));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {  
        	closeConn();  
        } 
		if (list!=null && list.size()>0) {
			return list.get(0);
		} 
		return null;
    }  
    
    /** 
     * 组装list对象 
     * @author linbingwen 
     * @since  2016年10月20日  
     * @param clazz 
     * @param rs 
     * @return 
     * @throws Exception 
     */  
    private static Object handler(Class clazz, ResultSet rs) throws Exception {  
        List list = new ArrayList();  
        try {  
            while (rs.next()) {  
                Object bean = clazz.newInstance();  
                
                ResultSetMetaData meta = rs.getMetaData();  
                int count = meta.getColumnCount();  
                for (int i = 0; i < count; i++) {  
                    String columnName = meta.getColumnName(i + 1);  
                    String name = columnName;  
                    if (columnName.contains(".")) {  
                        String[] split = columnName.split("\\.");  
                        if (split.length != 2) {  
                            throw  new Exception("输入的表名不正确！");  
                        }  
                        name =split[1];  
                    }  
                      
                    Object value = rs.getObject(columnName);  
                    name = underlineToCamel(name.toLowerCase());  
                    try {  
                        Field f = bean.getClass().getDeclaredField(name);  
                        if (f != null) {  
                            f.setAccessible(true);  
                            f.set(bean, value);  
                        }  
                    } catch (NoSuchFieldException e) {  
                        logger.error("表中字段：{"+name+"}在类：{"+clazz+"}没有对应的属性");  
                    } catch (IllegalArgumentException e) {  
                    	e.printStackTrace();
                        logger.error("表中字段：{"+name+"}在类：{"+clazz+"}对应属性类型不一致");  
                    }  
                }  
                list.add(bean);  
            }  
        } catch (Exception e) {  
        	e.printStackTrace();
            throw e;  
        }  
          
        logger.info("hiveHandler successed the total size is:{"+list.size()+"}");  
          
        return list;  
    }  
    
    /** 
     * 下划线字段转成陀峰字段 
     * @author linbingwen 
     * @since  2016年10月20日  
     * @param param 
     * @return 
     */  
    private static String underlineToCamel(String param) {  
        if (param == null || param.isEmpty()) {  
            return null;  
        }  
        int len = param.length();  
        StringBuilder sb = new StringBuilder(len);  
        for (int i = 0; i < len; i++) {  
            char c = param.charAt(i);  
            if (c == UNDERLINE) {  
                if (++i < len) {  
                    sb.append(Character.toUpperCase(param.charAt(i)));  
                }  
            } else {  
                sb.append(c);  
            }  
        }  
        return sb.toString();  
    }  
    
    
    public static List<Map<String, String>> exeSql(String sql)
    {
    	logger.info("sql = {"+sql+"}");
        Connection conn = null;
        Statement stmt = null;
        List<Map<String, String>> result = new ArrayList<Map<String, String>>();
        try
        {
            conn = getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            int columnCount = rs.getMetaData().getColumnCount();

            while (rs.next())
            {
            	Map<String, String> map = new HashMap<String, String>();
                for (int i = 1; i <= columnCount; i++)
                {
                	map.put(rs.getMetaData().getColumnName(i), rs.getString(i));
                }
                result.add(map);
            }
            return result;

        }
        catch (Exception e)
        {
        	logger.error("execute sql " + sql + " error:");
        	logger.error(e.getMessage());
            result = new ArrayList<Map<String, String>>();
            return result;
        }
        finally
        {
        	closeConn();  
        }
    }
	
}
